﻿/*工序表*/
--drop table TProcedure
create table TProcedure
(
	ProcedureNo  varchar(10) ,--工序号
	ProcedureName  varchar(50) ,--工序名称
	PStatus int default 0,--状态
	ParentNumber  varchar(10),--父工序号代码
	primary key (ProcedureNo)
)
go

--Bom主表
--drop table TBomMain
CREATE TABLE TBomMain
(	
	ProductItemId   int   not null,--产品内码***
	ProductItemNumber   varchar(300) ,--产品代码
	ProductItemName   varchar(300) ,--产品名称
	ProductVersion   varchar(200)   not null ,--版本***
	CheckStatus  int default 0,--状态，1，已经审核，0，未审核	
	CheckId   varchar(50) ,--审核人id
	CheckDate  datetime ,--审核日期
	UnCheckId   varchar(50) ,--反审核人id
	UnCheckDate  datetime ,--审核日期
	ImportStatus  int default 1,--导入状态，1，成功导入，0，未成功导入
	ImportId   varchar(50) ,--导入人id
	ImportDate  datetime ,--导入日期
	ExtChar1  varchar(50) ,--备用1
	ExtChar2  varchar(50) ,--备用2
	ExtChar3  varchar(50) ,--备用3
	ExtChar4  varchar(50) ,--备用4
	ExtChar5  varchar(50) ,--备用5
	ExtChar6  varchar(50) ,--备用6
	ExtChar7  varchar(50) ,--备用7
	ExtChar8  varchar(50) ,--备用8
	ExtChar9  varchar(50) ,--备用9
	ExtChar10  varchar(50) ,--备用10	
	ExtNumeric1   decimal(28, 10) ,--备用1
	ExtNumeric2   decimal(28, 10) ,--备用2
	ExtNumeric3   decimal(28, 10) ,--备用3
	ExtNumeric4  decimal(28, 10) ,--备用4
	ExtNumeric5   decimal(28, 10),--备用5
	ExtNumeric6   decimal(28, 10) ,--备用6
	ExtNumeric7  decimal(28, 10) ,--备用7
	ExtNumeric8   decimal(28, 10) ,--备用8
	ExtNumeric9   decimal(28, 10) ,--备用9
	ExtNumeric10  decimal(28, 10) ,--备用10
	primary key (ProductItemId,ProductVersion)
)
go

--Bom子表
--drop table TBomEntry
CREATE TABLE TBomEntry
(	
	ProductItemId   int   not null,--产品内码***
	ProductItemNumber   varchar(300) ,--产品代码
	ProductItemName   varchar(300) ,--产品名称
	ProductVersion   varchar(200)   not null ,--版本***
	Idx int ,--序号
	MaterialItemId   int ,--原材料内码
	MaterialItemNumber   varchar(300) ,---原材料代码
	MaterialItemName   varchar(300) ,---原材料名称
	Qty  decimal(28, 4) default 0,--数量
	ProcedureNo  varchar(255)   not null ,--工序号
	ProcedureName  varchar(255) ,--工序名称
	ImportStatus  int default 1,--导入状态，1，成功导入，0，未成功导入
	ImportId   varchar(50) ,--导入人id
	ImportDate  datetime ,--导入日期
	ExtChar1  varchar(50) ,--备用1
	ExtChar2  varchar(50) ,--备用2
	ExtChar3  varchar(50) ,--备用3
	ExtChar4  varchar(50) ,--备用4
	ExtChar5  varchar(50) ,--备用5
	ExtChar6  varchar(50) ,--备用6
	ExtChar7  varchar(50) ,--备用7
	ExtChar8  varchar(50) ,--备用8
	ExtChar9  varchar(50) ,--备用9
	ExtChar10  varchar(50) ,--备用10	
	ExtNumeric1   decimal(28, 10) ,--备用1
	ExtNumeric2   decimal(28, 10) ,--备用2
	ExtNumeric3   decimal(28, 10) ,--备用3
	ExtNumeric4  decimal(28, 10) ,--备用4
	ExtNumeric5   decimal(28, 10),--备用5
	ExtNumeric6   decimal(28, 10) ,--备用6
	ExtNumeric7  decimal(28, 10) ,--备用7
	ExtNumeric8   decimal(28, 10) ,--备用8
	ExtNumeric9   decimal(28, 10) ,--备用9
	ExtNumeric10  decimal(28, 10) ,--备用10
	primary key (ProductItemId,ProductVersion,MaterialItemId)
)
go

--创建Bom表，作废
/*
CREATE TABLE TBomHeader
(
	RowGuid varchar(32) NOT NULL, --主键
	BomNumber varchar(300) ,--bom的编号
	ParentItemId   int ,--父节点内码
	ParentItemNumber   varchar(300) ,--父节点代码
	ParentItemName   varchar(300) ,--父节点名称
	ParentType  int default 1,--子节点类型，1，成品，2，半成品
	ChildItemId   int ,--子节点内码
	ChildItemNumber   varchar(300) ,--子节点代码
	ChildItemName   varchar(300) ,--子节点名称
	ChildType  int default 3,--子节点类型，1，成品，2，半成品，3，原材料
	Qty  decimal(28, 10) default 0,--数量
	ProcessNo  varchar(10) ,--工序号
	ProcessName  varchar(50) ,--工序名称
	UseStatus  int default 1,--状态，1，使用中，0，作废中
	FVersion   varchar(300) ,--版本
	ImportStatus  int default 1,--导入状态，1，成功导入，0，未成功导入
	ImportId   varchar(50) ,--导入人id
	ImportDate  datetime ,--导入日期
	CheckId   varchar(50) ,--审核人id
	CheckDate  datetime ,--审核日期
	ExtChar1  varchar(50) ,--备用1
	ExtChar2  varchar(50) ,--备用2
	ExtChar3  varchar(50) ,--备用3
	ExtChar4  varchar(50) ,--备用4
	ExtChar5  varchar(50) ,--备用5
	ExtChar6  varchar(50) ,--备用6
	ExtChar7  varchar(50) ,--备用7
	ExtChar8  varchar(50) ,--备用8
	ExtChar9  varchar(50) ,--备用9
	ExtChar10  varchar(50) ,--备用10	
	ExtNumeric1   decimal(28, 10) ,--备用1
	ExtNumeric2   decimal(28, 10) ,--备用2
	ExtNumeric3   decimal(28, 10) ,--备用3
	ExtNumeric4  decimal(28, 10) ,--备用4
	ExtNumeric5   decimal(28, 10),--备用5
	ExtNumeric6   decimal(28, 10) ,--备用6
	ExtNumeric7  decimal(28, 10) ,--备用7
	ExtNumeric8   decimal(28, 10) ,--备用8
	ExtNumeric9   decimal(28, 10) ,--备用9
	ExtNumeric10  decimal(28, 10) ,--备用10
	primary key (RowGuid)
);
*/

--生产参数情况记录表
--drop table TProductBase
CREATE TABLE TProductBase
(
	RowGuid varchar(32) NOT NULL, --主键
	OperateMember  varchar(500) ,--参与人员
	MachineState  varchar(500) ,--机器状态
	GoodsState  varchar(500) ,--物料情况
	PdState varchar(500), --生产情况
	ProduceWay  varchar(500) ,--生产方法
	ProduceEnvironment varchar(500) ,--生产环境
	RepairNote varchar(255), --维修说明
	TranType int default 1 ,      --类型：1串码，2维修
	InputId   varchar(50) ,--输入人id
	InputName   varchar(50) ,--输入人
	InputDate  datetime ,--输入日期
	CheckId   varchar(50) ,--审核人id
	CheckDate  datetime ,--审核日期
	OperateId   varchar(50) ,--操作人id
	OperateDate  datetime ,--操作日期
	ExtChar1  varchar(50) ,--备用1 
	ExtChar2  varchar(50) ,--备用2
	ExtChar3  varchar(50) ,--备用3
	ExtChar4  varchar(50) ,--备用4
	ExtNumeric1   decimal(28, 10) ,--备用1
	ExtNumeric2   decimal(28, 10) ,--备用2
	ExtNumeric3   decimal(28, 10) ,--备用3
	ExtNumeric4  decimal(28, 10) ,--备用4
	primary key (RowGuid)
);
go

--维修记录
--drop table TRepairHeader
CREATE TABLE TRepairHeader
(
	ProductItemBarCode   varchar(100) not null,--维修成品/物料的条码，主键
	ProductBaseRowGuid varchar(32) NOT NULL, --TProductBase的主键
	ProductItemId   int ,--成品/半成品的物料内码
	ProductItemNumber   varchar(300) ,--产品代码
	ProductItemName   varchar(300) ,--产品名称
	OrderId  varchar(100) ,--订单号
	NewProductItemBarCode  varchar(100) not null,
	NewProductItemId   int ,--成品/半成品的物料内码
	NewProductItemNumber   varchar(300) ,--产品代码
	NewProductItemName   varchar(300) ,--产品名称
	ProcedureNo  varchar(255) ,--工序号
	ProcedureName  varchar(255) ,--工序名称
	UseStatus  int default 1,--状态，1，使用中，0，作废中
	InputId   varchar(50) ,--输入人id
	InputDate  datetime ,--输入日期，采集日期
	CheckId   varchar(50) ,--审核人id
	CheckDate  datetime ,--审核日期
	ExtChar1  varchar(50) ,--备用1 
	ExtChar2  varchar(50) ,--备用2
	ExtChar3  varchar(50) ,--备用3
	ExtChar4  varchar(50) ,--备用4
	ExtNumeric1   decimal(28, 10) ,--备用1
	ExtNumeric2   decimal(28, 10) ,--备用2
	ExtNumeric3   decimal(28, 10) ,--备用3
	ExtNumeric4  decimal(28, 10) ,--备用4
	primary key (ProductItemBarCode)
);
go

--成品/半成品，一个成品/半成品标签一条记录
--drop table TProductHeader
CREATE TABLE TProductHeader
(
	ProductItemBarCode   varchar(100) not null,--成品/半成品的物料实例条码，主键
	ProcedureNo  varchar(255) not null ,--工序号
	ProcedureName  varchar(255) ,--工序名称
	ProductBaseRowGuid varchar(32) NOT NULL, --TProductBase的主键
	ProductItemId   int ,--成品/半成品的物料内码
	ProductItemNumber   varchar(300) ,--产品代码
	ProductItemName   varchar(300) ,--产品名称
	OrderId  varchar(100) ,--订单号
	UseStatus  int default 1,--状态，1，使用中，0，作废中
	TranType int default 1 ,      --类型：1串码，2维修，3元件
	InputId   varchar(50) ,--输入人id
	InputDate  datetime ,--输入日期，采集日期
	CheckId   varchar(50) ,--审核人id
	CheckDate  datetime ,--审核日期
	ExtChar1  varchar(50) ,--备用1 ==   订单另外加了字段了201012011。 挪用作为订单号码-- 20101208 added by henrik
	ExtChar2  varchar(50) ,--备用2
	ExtChar3  varchar(50) ,--备用3
	ExtChar4  varchar(50) ,--备用4
	ExtChar5  varchar(50) ,--备用5
	ExtChar6  varchar(50) ,--备用6
	ExtChar7  varchar(50) ,--备用7
	ExtChar8  varchar(50) ,--备用8
	ExtChar9  varchar(50) ,--备用9
	ExtChar10  varchar(50) ,--备用10	
	ExtNumeric1   decimal(28, 10) ,--备用1
	ExtNumeric2   decimal(28, 10) ,--备用2
	ExtNumeric3   decimal(28, 10) ,--备用3
	ExtNumeric4  decimal(28, 10) ,--备用4
	ExtNumeric5   decimal(28, 10),--备用5
	ExtNumeric6   decimal(28, 10) ,--备用6
	ExtNumeric7  decimal(28, 10) ,--备用7
	ExtNumeric8   decimal(28, 10) ,--备用8
	ExtNumeric9   decimal(28, 10) ,--备用9
	ExtNumeric10  decimal(28, 10) ,--备用10
	primary key (ProductItemBarCode,ProcedureNo)
);
go

--半成品/原材料，一个成品/半成品对应多个半成品原材料的记录、
--drop table TProductDetail
CREATE TABLE TProductDetail
(
	RowGuid varchar(32) NOT NULL, --主键	
	ParentProductItemBarCode   varchar(100)  not null ,--成品/半成品的物料实例条码，外键，TProductHeader的主键
	ProcedureNo  varchar(255) not null ,--工序号
	ProcedureName  varchar(255) ,--工序名称
	ChildItemBarCode   varchar(300)  not null ,--半成品/原材料的物料实例条码
	ChildItemId   int not null ,--半成品/原材料的物料内码
	ChildItemNumber   varchar(300) ,--产品代码
	ChildItemName   varchar(300) ,--产品名称
	Idx  int default 1,--索引顺序号码，同一个物料内码一组编号
	UseStatus  int default 1,--状态，1，使用中，0，作废中
	InputId   varchar(50) ,--输入人id
	InputDate  datetime ,--输入日期
	CheckId   varchar(50) ,--审核人id
	CheckDate  datetime ,--审核日期
	ExtChar1  varchar(50) ,--备用1
	ExtChar2  varchar(50) ,--备用2
	ExtChar3  varchar(50) ,--备用3
	ExtChar4  varchar(50) ,--备用4
	ExtChar5  varchar(50) ,--备用5
	ExtChar6  varchar(50) ,--备用6
	ExtChar7  varchar(50) ,--备用7
	ExtChar8  varchar(50) ,--备用8
	ExtChar9  varchar(50) ,--备用9
	ExtChar10  varchar(50) ,--备用10	
	ExtNumeric1   decimal(28, 10) ,--备用1
	ExtNumeric2   decimal(28, 10) ,--备用2
	ExtNumeric3   decimal(28, 10) ,--备用3
	ExtNumeric4  decimal(28, 10) ,--备用4
	ExtNumeric5   decimal(28, 10),--备用5
	ExtNumeric6   decimal(28, 10) ,--备用6
	ExtNumeric7  decimal(28, 10) ,--备用7
	ExtNumeric8   decimal(28, 10) ,--备用8
	ExtNumeric9   decimal(28, 10) ,--备用9
	ExtNumeric10  decimal(28, 10) ,--备用10
	primary key (RowGuid)
);
go

--创建视图
--drop view VIcItemQuery
create view VIcItemQuery
as
select FItemId,FNumber,FName,FModel,FErpClsId from dbo.t_icitem;	
go

--主表和明细表的联合
--drop view VBomEntryAndMain		
create view VBomEntryAndMain
as 
select e.*,m.CheckStatus,m.CheckId,m.CheckDate,m.UnCheckId,m.UnCheckDate,m.ExtChar1 as CheckName 
from 
(
select tmp.*,k.FNumber as MaterialItemNumber,k.FName as MaterialItemName from (
SELECT ProductItemId,i.FNumber as ProductItemNumber,i.FName as ProductItemName,ProductVersion,MaterialItemId,Idx,Qty,ProcedureNo,ProcedureName,ImportStatus,ImportId,ImportDate,ExtChar1,ExtChar2,ExtChar3,ExtChar4,ExtChar5,ExtChar6,ExtChar7,ExtChar8,ExtChar9,ExtChar10,ExtNumeric1,ExtNumeric2,ExtNumeric3,ExtNumeric4,ExtNumeric5,ExtNumeric6,ExtNumeric7,ExtNumeric8,ExtNumeric9,ExtNumeric10 FROM TBomEntry m,VIcItemQuery i  WHERE m.ProductItemId=i.FItemId
) tmp ,VIcItemQuery k where tmp.MaterialItemId = k.FItemId 
) e 
left outer join 
(
SELECT ProductItemId,i.FNumber as ProductItemNumber,i.FName as ProductItemName,ProductVersion,CheckStatus,CheckId,CheckDate,UnCheckId,UnCheckDate,ImportStatus,ImportId,ImportDate,ExtChar1,ExtChar2,ExtChar3,ExtChar4,ExtChar5,ExtChar6,ExtChar7,ExtChar8,ExtChar9,ExtChar10,ExtNumeric1,ExtNumeric2,ExtNumeric3,ExtNumeric4,ExtNumeric5,ExtNumeric6,ExtNumeric7,ExtNumeric8,ExtNumeric9,ExtNumeric10 FROM TBomMain m,VIcItemQuery i where m.ProductItemId = i.FItemId
) m 
on  e.ProductItemId =  m.ProductItemId and e.ProductVersion = m.ProductVersion;
go
				
--串码base，主表和明细表的联合
--drop view VProductDetailHeaderBase		
create view VProductDetailHeaderBase
as 
select d.*,h.ProductItemId,h.ProductItemNumber,h.ProductItemName,h.OrderId,
b.OperateMember,b.MachineState,b.GoodsState,b.PdState,b.ProduceWay,b.ProduceEnvironment
 from 
	 (
	SELECT RowGuid,ParentProductItemBarCode,ProcedureNo,ProcedureName,ChildItemBarCode,ChildItemId,i.FNumber as ChildItemNumber,i.FName as ChildItemName,Idx,UseStatus,InputId,InputDate,CheckId,CheckDate,ExtChar1,ExtChar2,ExtChar3,ExtChar4,ExtChar5,ExtChar6,ExtChar7,ExtChar8,ExtChar9,ExtChar10,ExtNumeric1,ExtNumeric2,ExtNumeric3,ExtNumeric4,ExtNumeric5,ExtNumeric6,ExtNumeric7,ExtNumeric8,ExtNumeric9,ExtNumeric10 FROM TProductDetail m,VIcItemQuery i where m.ChildItemId = i.FItemId
	)  d, 
  ( 
	SELECT ProductItemBarCode,ProcedureNo,ProcedureName,ProductBaseRowGuid,ProductItemId,i.FNumber as ProductItemNumber, i.FName as ProductItemName,OrderId,UseStatus,TranType,InputId,InputDate,CheckId,CheckDate,ExtChar1,ExtChar2,ExtChar3,ExtChar4,ExtChar5,ExtChar6,ExtChar7,ExtChar8,ExtChar9,ExtChar10,ExtNumeric1,ExtNumeric2,ExtNumeric3,ExtNumeric4,ExtNumeric5,ExtNumeric6,ExtNumeric7,ExtNumeric8,ExtNumeric9,ExtNumeric10 FROM TProductHeader m,VIcItemQuery i where m.ProductItemId = i.FItemId
	)    h ,
	dbo.TProductBase b
where d.ParentProductItemBarCode =  h.ProductItemBarCode 
and d.ProcedureNo =  h.ProcedureNo 
and h.ProductBaseRowGuid = b.RowGuid;
go					

--base和维修的联合
--drop view VRepairBase		
create view VRepairBase
as 
select r.*,b.OperateMember,b.MachineState,b.GoodsState,b.PdState,b.ProduceWay,b.ProduceEnvironment,b.RepairNote
 from  (
select tmp.*,k.FNumber as NewProductItemNumber,k.FName as NewProductItemName from (
SELECT ProductItemBarCode,ProductBaseRowGuid,ProductItemId,i.FNumber as ProductItemNumber, i.FName as ProductItemName,OrderId,NewProductItemBarCode,NewProductItemId,ProcedureNo,ProcedureName,UseStatus,InputId,InputDate,CheckId,CheckDate,ExtChar1,ExtChar2,ExtChar3,ExtChar4,ExtNumeric1,ExtNumeric2,ExtNumeric3,ExtNumeric4 FROM TRepairHeader  m,VIcItemQuery i where m.ProductItemId = i.FItemId
) tmp ,VIcItemQuery k where tmp.NewProductItemId = k.FItemId
)  r ,dbo.TProductBase b
where  b.RowGuid =  r.ProductBaseRowGuid ;
go	

--base和维修的联合
--drop view VComponentScanBase		
create view VComponentScanBase
as 
select h.*,b.OperateMember,b.MachineState,b.GoodsState,b.PdState,b.ProduceWay,b.ProduceEnvironment
 from 
  ( 
  	SELECT ProductItemBarCode,ProcedureNo,ProcedureName,ProductBaseRowGuid,ProductItemId,i.FNumber as ProductItemNumber, i.FName as ProductItemName,OrderId,UseStatus,TranType,InputId,InputDate,CheckId,CheckDate,ExtChar1,ExtChar2,ExtChar3,ExtChar4,ExtChar5,ExtChar6,ExtChar7,ExtChar8,ExtChar9,ExtChar10,ExtNumeric1,ExtNumeric2,ExtNumeric3,ExtNumeric4,ExtNumeric5,ExtNumeric6,ExtNumeric7,ExtNumeric8,ExtNumeric9,ExtNumeric10 FROM TProductHeader m,VIcItemQuery i where m.ProductItemId = i.FItemId
  ) h ,	dbo.TProductBase b
where h.ProductBaseRowGuid = b.RowGuid;
go	